﻿using ExcelAdaptorLib;
using Microsoft.Data.SqlClient;
using SMTUploadProductionData.model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Documents;

namespace gl_SMT.Model
{
    internal class DatabaseReadWrite
    {
        SqlConnection connection;
        string connectionString = "Data Source=192.168.5.97;Initial Catalog=SMTMachineProduce;User ID=sa;Password=gl.2008;TrustServerCertificate=True";
        public DatabaseReadWrite(ref string exception)
        {
            try
            {
                connection = new SqlConnection(connectionString);
                connection.Open();
            }
            catch (Exception ex)
            {
                exception = ex.ToString();
                return;
            }
        }

        public DatabaseReadWrite()
        {
            connection = new SqlConnection(connectionString);
            connection.Open();
        }


        public async Task<List<T>> QueryDataAsync<T>(string TabName, string colName, string content, string time) where T : new()
        {
            var result = new List<T>();
            string selectString = $"select * from {TabName} where {colName}= '{content}' and CAST({time} AS DATE)=CAST(GETDATE() AS DATE)";

            using (var connect = new SqlConnection(connectionString))
            {
                await connect.OpenAsync();
                using (var command = new SqlCommand(selectString, connect))
                {
                    using (var dr = await command.ExecuteReaderAsync())
                    {
                        var properties = typeof(T).GetProperties().ToDictionary(p => p.Name, StringComparer.OrdinalIgnoreCase);

                        while (await dr.ReadAsync())
                        {
                            var item = new T();
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                var columnName = dr.GetName(i);
                                if (properties.TryGetValue(columnName, out var property) && !dr.IsDBNull(i))
                                {
                                    var value = dr.GetValue(i);
                                    var convertedValue = Convert.ChangeType(value, property.PropertyType);
                                    property.SetValue(item, convertedValue);
                                }
                            }
                            result.Add(item);
                        }
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 获取在线机台名称
        /// </summary>
        /// <param name="TabName"></param>
        /// <returns></returns>
        public async Task<List<string>> SelectColNameAsync(string TabName)
        {
            List<string> list_Name = new List<string>();
            string select = $"SELECT DISTINCT MachineName FROM {TabName}";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(select, sqlConnection))
                {
                    using (SqlDataReader dr = await command.ExecuteReaderAsync())
                    {
                        while (dr.Read())
                        {
                            list_Name.Add(dr["MachineName"].ToString());
                        }
                    }
                }
            }
            return list_Name;
        }
        /// <summary>
        /// 查询机台在线数量
        /// </summary>
        public async Task<List<string>> SelectDataAsync(string TabName, string colName)
        {
            List<string?> list = new List<string?>();
            string str_Select = $"SELECT DISTINCT MachineName FROM {TabName} where CAST({colName} AS DATE)=CAST(GETDATE() AS DATE)";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    SqlDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        list.Add(dr["MachineName"].ToString());
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 查询gkg报警数量
        /// </summary>
        /// <returns></returns>
        public async Task<int> SelectOPEventNumber()
        {
            string Select = "SELECT COUNT(*) FROM gkg_OPEvent WHERE CAST(Event_Day AS DATE) = CAST(GETDATE() AS DATE)";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(Select, sqlConnection))
                {
                    object? result = await command.ExecuteScalarAsync();
                    int count = result != null ? (int)result : 0;
                    return count;
                }
            }
        }

        public async Task<List<gkg_Event_model>> SelectEvent(string colName)
        {
            List<gkg_Event_model> gkg_OPEvent_Models = new List<gkg_Event_model>();
            string str_Select = $"SELECT * FROM gkg_Event WHERE MachineName='{colName}' AND CAST(Event_Day AS DATE)=CAST(GETDATE() AS DATE)";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    using (SqlDataReader dr = await command.ExecuteReaderAsync())
                    {
                        while (await dr.ReadAsync())
                        {
                            gkg_Event_model gkg_OPEvent = new gkg_Event_model();
                            Type type = gkg_OPEvent.GetType();
                            PropertyInfo[] properties = type.GetProperties();
                            foreach (PropertyInfo property in properties)
                            {
                                string propertyName = property.Name;
                                int columnIndex = dr.GetOrdinal(propertyName);
                                if (dr[columnIndex] != DBNull.Value)
                                {
                                    object valueFromDb = dr[columnIndex];
                                    // 获取属性的目标类型
                                    Type targetType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                                    if (targetType == typeof(int))
                                    {
                                        property.SetValue(gkg_OPEvent, Convert.ToInt32(valueFromDb));
                                    }
                                    else
                                    {
                                        property.SetValue(gkg_OPEvent, valueFromDb);
                                    }
                                }
                            }
                            gkg_OPEvent_Models.Add(gkg_OPEvent);
                        }
                    }
                }
                return gkg_OPEvent_Models;
            }
        }


        /// <summary>
        /// 查询gkg印刷机不同机型的最新一条数据
        /// </summary>
        /// <param name="gkgs"></param>
        /// <returns></returns>
        public async Task SelectDataAsync(List<gkg_model> gkgs)
        {
            string str_Select = $"WITH TodayData AS(SELECT *, ROW_NUMBER() OVER (PARTITION BY MachineName ORDER BY TimeStamp DESC) AS rn" +
                $" FROM gkg_Producedate WHERE CAST(TimeStamp AS DATE) = CAST(GETDATE() AS DATE)) SELECT * FROM TodayData WHERE rn = 1";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    using (SqlDataReader dr = await command.ExecuteReaderAsync())
                    {
                        while (await dr.ReadAsync())
                        {
                            gkg_model gkg = new gkg_model();
                            Type type = gkg.GetType();
                            PropertyInfo[] properties = type.GetProperties();
                            foreach (PropertyInfo property in properties)
                            {
                                string propertyName = property.Name;
                                int columnIndex = dr.GetOrdinal(propertyName);
                                if (dr[columnIndex] != DBNull.Value)
                                {
                                    object valueFromDb = dr[columnIndex];
                                    // 获取属性的目标类型
                                    Type targetType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                                    if (targetType == typeof(int))
                                    {
                                        property.SetValue(gkg, Convert.ToInt32(valueFromDb));
                                    }
                                    else
                                    {
                                        property.SetValue(gkg, valueFromDb);
                                    }
                                }
                            }
                            gkgs.Add(gkg);
                        }
                    }
                }
            }
        }


        public async Task SelectDataAsync(List<ymh_DayNightShifts_model> DayNightShift, string Classes)
        {
            string str_Select = $"WITH TodayData AS(SELECT *, ROW_NUMBER() OVER (PARTITION BY MachineName ORDER BY Time DESC) AS rn" +
                $" FROM ymh_DayNightShifts WHERE TimePeriod='{Classes}' and CAST(Time AS DATE) = CAST(GETDATE() AS DATE)) SELECT * FROM TodayData WHERE rn = 1";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    using (SqlDataReader dr = await command.ExecuteReaderAsync())
                    {
                        while (await dr.ReadAsync())
                        {
                            ymh_DayNightShifts_model DayNightShifts = new ymh_DayNightShifts_model();
                            Type type = DayNightShifts.GetType();
                            PropertyInfo[] properties = type.GetProperties();
                            foreach (PropertyInfo property in properties)
                            {
                                string propertyName = property.Name;
                                int columnIndex = dr.GetOrdinal(propertyName);
                                if (dr[columnIndex] != DBNull.Value)
                                {
                                    object valueFromDb = dr[columnIndex];
                                    // 获取属性的目标类型
                                    Type targetType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                                    if (targetType == typeof(int))
                                    {
                                        property.SetValue(DayNightShifts, Convert.ToInt32(valueFromDb));
                                    }
                                    else
                                    {
                                        property.SetValue(DayNightShifts, valueFromDb);
                                    }
                                }
                            }
                            DayNightShift.Add(DayNightShifts);
                        }
                    }
                }
            }
        }


        public async Task SelectDataAsync(List<juki_model> jukis)
        {
            string str_Select = $"SELECT * FROM juki_Producedate WHERE  CAST(Time AS DATE)=CAST(GETDATE() AS DATE)";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    using (SqlDataReader dr = await command.ExecuteReaderAsync())
                    {
                        while (await dr.ReadAsync())
                        {
                            juki_model juki = new juki_model();
                            Type type = juki.GetType();
                            PropertyInfo[] properties = type.GetProperties();
                            foreach (PropertyInfo property in properties)
                            {
                                string propertyName = property.Name;
                                int columnIndex = dr.GetOrdinal(propertyName);
                                if (dr[columnIndex] != DBNull.Value)
                                {
                                    object valueFromDb = dr[columnIndex];
                                    // 获取属性的目标类型
                                    Type targetType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                                    if (targetType == typeof(int))
                                    {
                                        property.SetValue(juki, Convert.ToInt32(valueFromDb));
                                    }
                                    else
                                    {
                                        property.SetValue(juki, valueFromDb);
                                    }
                                }
                            }
                            jukis.Add(juki);
                        }
                    }
                }
            }
        }

        public async Task SelectDataAsync(List<hl_model> hls)
        {
            string str_Select = $"WITH TodayData AS(SELECT *, ROW_NUMBER() OVER (PARTITION BY MachineName ORDER BY logTime DESC) AS rn" +
                $" FROM hl_Producedate WHERE CAST(logTime AS DATE) = CAST(GETDATE() AS DATE)) SELECT * FROM TodayData WHERE rn = 1";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    using (SqlDataReader dr = await command.ExecuteReaderAsync())
                    {
                        while (await dr.ReadAsync())
                        {
                            hl_model hl = new hl_model();
                            Type type = hl.GetType();
                            PropertyInfo[] properties = type.GetProperties();
                            foreach (PropertyInfo property in properties)
                            {
                                string propertyName = property.Name;
                                int columnIndex = dr.GetOrdinal(propertyName);
                                if (dr[columnIndex] != DBNull.Value)
                                {
                                    object valueFromDb = dr[columnIndex];
                                    // 获取属性的目标类型
                                    Type targetType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                                    if (targetType == typeof(int))
                                    {
                                        property.SetValue(hl, Convert.ToInt32(valueFromDb));
                                    }
                                    else
                                    {
                                        property.SetValue(hl, valueFromDb);
                                    }
                                }
                            }
                            hls.Add(hl);
                        }
                    }
                }
            }
        }

        public async Task SelectDataAsync(List<ymh_model> ymhs)
        {
            string str_Select = $"WITH TodayData AS(SELECT *, ROW_NUMBER() OVER (PARTITION BY MachineName ORDER BY Time DESC) AS rn" +
                $" FROM ymh_Producedate WHERE CAST(Time AS DATE) = CAST(GETDATE() AS DATE)) SELECT * FROM TodayData WHERE rn = 1";
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    using (SqlDataReader dr = await command.ExecuteReaderAsync())
                    {
                        while (await dr.ReadAsync())
                        {
                            ymh_model ymh = new ymh_model();
                            Type type = ymh.GetType();
                            PropertyInfo[] properties = type.GetProperties();
                            foreach (PropertyInfo property in properties)
                            {
                                string propertyName = property.Name;
                                int columnIndex = dr.GetOrdinal(propertyName);
                                if (dr[columnIndex] != DBNull.Value)
                                {
                                    object valueFromDb = dr[columnIndex];
                                    // 获取属性的目标类型
                                    Type targetType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                                    if (targetType == typeof(int))
                                    {
                                        property.SetValue(ymh, Convert.ToInt32(valueFromDb));
                                    }
                                    else
                                    {
                                        property.SetValue(ymh, valueFromDb);
                                    }
                                }
                            }
                            ymhs.Add(ymh);
                        }
                    }
                }
            }
        }

        public async Task<DataTable> gkg_SelectDataAsync(string TabName, string content)
        {
            string str_Select = $"select * from {TabName} where MachineName ='{content}' and CAST(TimeStamp AS DATE)=CAST(GETDATE() AS DATE)";
            DataTable dt = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(str_Select, sqlConnection))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(command))
                    {
                        da.Fill(dt);
                    }
                }
            }
            return dt;
        }

        public async Task<DataTable> SelectDataAsync(string Select)
        {
            DataTable dt = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                await sqlConnection.OpenAsync();
                using (SqlCommand command = new SqlCommand(Select, connection))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(command))
                    {
                        da.Fill(dt);
                    }
                }
            }
            return dt;
        }

        public DataTable SelectData(string Select)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlCommand command = new SqlCommand(Select, connection))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(command))
                    {
                        da.Fill(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }


            return dt;
        }

        /// <summary>
        /// 在线机台名称
        /// </summary>
        /// <param name="TabName"></param>
        /// <returns></returns>
        public List<string> SelectColName(string TabName)
        {
            List<string> list_Name = new List<string>();
            string select = $"SELECT DISTINCT MachineName FROM {TabName}";
            using (SqlCommand command = new SqlCommand(select, connection))
            {
                using (SqlDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        list_Name.Add(dr["MachineName"].ToString());
                    }
                }
            }
            return list_Name;
        }

        public List<T> QueryData<T>(string tabName, string colName, string content, string Stime, string Etime, string Time, string date) where T : new()
        {
            var result = new List<T>();
            string selectString = $"SELECT * FROM {tabName} WHERE {colName} = '{content}' AND CAST({Time} AS {date}) >= '{Stime}'and CAST({Time} AS {date}) <= '{Etime}'";
            using (var command = new SqlCommand(selectString, connection))
            {
                using (var dr = command.ExecuteReader())
                {
                    var properties = typeof(T).GetProperties().ToDictionary(p => p.Name, StringComparer.OrdinalIgnoreCase);

                    while (dr.Read())
                    {
                        var item = new T();
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            var columnName = dr.GetName(i);
                            if (properties.TryGetValue(columnName, out var property) && !dr.IsDBNull(i))
                            {
                                var value = dr.GetValue(i);
                                var convertedValue = Convert.ChangeType(value, property.PropertyType);
                                property.SetValue(item, convertedValue);
                            }
                        }
                        result.Add(item);
                    }
                }
            }
            return result;
        }

        public async Task<List<T>> QueryDataAsync<T>(string selectString) where T : new()
        {
            var result = new List<T>();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                using (SqlCommand command = new SqlCommand(selectString, connection))
                {
                    using (SqlDataReader dr = await command.ExecuteReaderAsync())
                    {
                        var properties = typeof(T).GetProperties().ToDictionary(p => p.Name, StringComparer.OrdinalIgnoreCase);
                        while (dr.Read())
                        {
                            var item = new T();
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                var columnName = dr.GetName(i);
                                if (properties.TryGetValue(columnName, out var property) && !dr.IsDBNull(i))
                                {
                                    var value = dr.GetValue(i);
                                    var convertedValue = Convert.ChangeType(value, property.PropertyType);
                                    property.SetValue(item, convertedValue);
                                }
                            }
                            result.Add(item);
                        }
                    }
                }
            }
            return result;
        }
    }
}
